package com.example.stella.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * 数据库帮助类 - 管理SQLite数据库的创建、升级和表结构
 */
public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "stella.db";
    private static final int DATABASE_VERSION = 1;

    // 表名常量
    public static final String TABLE_USERS = "users";
    public static final String TABLE_TRANSACTIONS = "transactions";
    public static final String TABLE_CATEGORIES = "categories";

    // 字段名常量
    public static final String COLUMN_ID = "id";
    public static final String COLUMN_USERNAME = "username";
    public static final String COLUMN_PASSWORD = "password";
    public static final String COLUMN_CREATED_AT = "created_at";
    public static final String COLUMN_USER_ID = "user_id";
    public static final String COLUMN_TYPE = "type";
    public static final String COLUMN_AMOUNT = "amount";
    public static final String COLUMN_CATEGORY = "category";
    public static final String COLUMN_DESCRIPTION = "description";
    public static final String COLUMN_DATE = "date";
    public static final String COLUMN_NAME = "name";
    public static final String COLUMN_ICON = "icon";

    // 用户表创建SQL
    private static final String CREATE_TABLE_USERS = 
        "CREATE TABLE " + TABLE_USERS + "(" +
        COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
        COLUMN_USERNAME + " TEXT UNIQUE NOT NULL," +
        COLUMN_PASSWORD + " TEXT NOT NULL," +
        COLUMN_CREATED_AT + " INTEGER NOT NULL" +
        ")";

    // 交易记录表创建SQL
    private static final String CREATE_TABLE_TRANSACTIONS = 
        "CREATE TABLE " + TABLE_TRANSACTIONS + "(" +
        COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
        COLUMN_USER_ID + " INTEGER NOT NULL," +
        COLUMN_TYPE + " TEXT NOT NULL," +
        COLUMN_AMOUNT + " REAL NOT NULL," +
        COLUMN_CATEGORY + " TEXT NOT NULL," +
        COLUMN_DESCRIPTION + " TEXT," +
        COLUMN_DATE + " INTEGER NOT NULL," +
        COLUMN_CREATED_AT + " INTEGER NOT NULL," +
        "FOREIGN KEY(" + COLUMN_USER_ID + ") REFERENCES " + TABLE_USERS + "(" + COLUMN_ID + ")" +
        ")";

    // 分类表创建SQL
    private static final String CREATE_TABLE_CATEGORIES = 
        "CREATE TABLE " + TABLE_CATEGORIES + "(" +
        COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
        COLUMN_NAME + " TEXT NOT NULL," +
        COLUMN_TYPE + " TEXT NOT NULL," +
        COLUMN_ICON + " TEXT" +
        ")";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    /**
     * 数据库首次创建时调用 - 创建所有表并插入默认数据
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_USERS);
        db.execSQL(CREATE_TABLE_TRANSACTIONS);
        db.execSQL(CREATE_TABLE_CATEGORIES);
        
        // 插入默认的收支分类
        insertDefaultCategories(db);
    }

    /**
     * 数据库版本升级时调用 - 删除旧表并重新创建
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_CATEGORIES);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TRANSACTIONS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
        onCreate(db);
    }

    /**
     * 插入默认的收入和支出分类
     */
    private void insertDefaultCategories(SQLiteDatabase db) {
        // 默认收入分类
        String[] incomeCategories = {"工资", "奖金", "兼职", "投资", "其他收入"};
        // 默认支出分类
        String[] expenseCategories = {"餐饮", "交通", "购物", "娱乐", "医疗", "住房", "教育", "其他支出"};

        // 插入收入分类
        for (String category : incomeCategories) {
            db.execSQL("INSERT INTO " + TABLE_CATEGORIES + "(" + COLUMN_NAME + "," + COLUMN_TYPE + "," + COLUMN_ICON + ") VALUES('" + category + "','收入','💰')");
        }

        // 插入支出分类
        for (String category : expenseCategories) {
            db.execSQL("INSERT INTO " + TABLE_CATEGORIES + "(" + COLUMN_NAME + "," + COLUMN_TYPE + "," + COLUMN_ICON + ") VALUES('" + category + "','支出','💸')");
        }
    }
} 